﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace BoekenUsb2.Model.DatabaseRepositories
{
    public abstract partial class DatabaseRepository
    {
        /************/
        /* Leerling */
        /************/

        public DataTable Leerling_GetData()
        {
            return defaultExecuteReaderDataTable(CommandType.Text, "SELECT leerlingID, naam, geboorteDatum, adminCode, straat, gemeente, groepCode FROM Leerling ORDER BY naam");
        }

        public DataTable Leerling_GetDataByGroepCodeGesorteerd(int groepCode)
        {
            DataTable dt = new DataTable();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@groepCode", groepCode);
                dbManager.ExecuteReader(CommandType.Text, "SELECT adminCode, geboorteDatum, gemeente, groepCode, leerlingID, naam, straat FROM Leerling WHERE (groepCode = @groepCode) ORDER BY naam");
                dt.Load(dbManager.DataReader);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.StackTrace);
            }
            finally
            {
                dbManager.Dispose();
            }
            return dt;
        }

        public DataTable Leerling_GetDataByGroepCode(int groepCode)
        {
            DataTable dt = new DataTable();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@groepCode", groepCode);
                dbManager.ExecuteReader(CommandType.Text, "SELECT adminCode, geboorteDatum, gemeente, groepCode, leerlingID, naam, straat FROM Leerling WHERE (groepCode = @groepCode)");
                dt.Load(dbManager.DataReader);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.StackTrace);
            }
            finally
            {
                dbManager.Dispose();
            }
            return dt;
        }

        public int Leerling_GetAantalByGroepCode(string groepCode)
        {
            int aantal = 0;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@groepCode", groepCode);
                aantal = Int32.Parse(dbManager.ExecuteScalar(CommandType.Text, "SELECT COUNT(*) FROM Leerling WHERE groepCode = @groepCode").ToString());
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.StackTrace);
            }
            finally
            {
                dbManager.Dispose();
            }
            return aantal;
        }

        public DataTable Leerling_GetLeerlingBesteltBoek(int boekID)
        {
            DataTable dt = new DataTable();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@boekID", boekID);
                dbManager.ExecuteReader(CommandType.Text, "SELECT adminCode, geboorteDatum, gemeente, groepCode, leerlingID, naam, straat FROM Leerling WHERE (leerlingID IN (SELECT leerlingID FROM LeerlingBoek WHERE (boekID = @boekID) AND (NOT (datumBestelling IS NULL))))");
                dt.Load(dbManager.DataReader);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.StackTrace);
            }
            finally
            {
                dbManager.Dispose();
            }
            return dt;
        }

        public DataTable Leerling_GetDataByLeerlingID(int leerlingID)
        {
            DataTable dt = new DataTable();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@leerlingID", leerlingID);
                dbManager.ExecuteReader(CommandType.Text, "SELECT adminCode, geboorteDatum, gemeente, groepCode, leerlingID, naam, straat FROM Leerling WHERE (leerlingID = @leerlingID)");
                dt.Load(dbManager.DataReader);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.StackTrace);
            }
            finally
            {
                dbManager.Dispose();
            }
            return dt;
        }

        public DataTable Leerling_GetDataByUniekeGroepCodes()
        {
            return defaultExecuteReaderDataTable(CommandType.Text, "SELECT DISTINCT groepCode FROM Leerling");
        }

        public void Leerling_TruncateDummy()
        {
            try
            {
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, "TRUNCATE TABLE Leerling");
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.StackTrace);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public int Leerling_UpdateGroepCodeToNull()
        {
            int aantal = 0;
            try
            {
                dbManager.Open();
                aantal = Int32.Parse(dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE Leerling SET groepCode = NULL").ToString());
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.StackTrace);
            }
            finally
            {
                dbManager.Dispose();
            }
            return aantal;
        }

        public int Leerling_UpdateGroepCode(int groepCode, int leerlingID)
        {
            int aantal = 0;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@groepCode", groepCode);
                dbManager.AddParameters(1, "@leerlingID", leerlingID);
                aantal = Int32.Parse(dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE Leerling SET groepCode = @groepCode WHERE leerlingID = @leerlingID").ToString());
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.StackTrace);
            }
            finally
            {
                dbManager.Dispose();
            }
            return aantal;
        }

        public int Leerling_UpdateGroepCodeByGroepCode(string nieuweGroepCode, string oudeGroepCode)
        {
            int aantal = 0;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@nieuweGroepCode", nieuweGroepCode);
                dbManager.AddParameters(1, "@oudeGroepCode", oudeGroepCode);
                aantal = Int32.Parse(dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE Leerling SET groepCode = @nieuweGroepCode WHERE groepCode = @oudeGroepCode").ToString());
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.StackTrace);
            }
            finally
            {
                dbManager.Dispose();
            }
            return aantal;
        }

        public bool Leerling_Truncate()
        {
            bool succeed = false;
            try
            {
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, "TRUNCATE TABLE Leerling");
                succeed = true;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.StackTrace);
            }
            finally
            {
                dbManager.Dispose();
            }
            return succeed;
        }

    }
}
